package com.ztc.ZtcOrder.dao;
import com.aqgj.util.Pager;
import com.ztc.ZtcOrder.bean.ZtcOrder;
import java.util.List;
import java.util.Map;

import com.ztc.ZtcTransport.bean.ZtcTransport;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import com.aqgj.util.BaseDaoImpl;

/**
 * @ClassName ZtcOrderDao
 * @author
 * @date   2017.10.29
 */
@Repository("ZtcOrderDao")
public class ZtcOrderDao extends BaseDaoImpl<ZtcOrder> {

	public String save(ZtcOrder ztcOrder) {
		this.getHibernateTemplate().save(ztcOrder);
		return ztcOrder.getId();
	}

	public Map getZcOrderByOrderNumber(String orderNumber) {
		String sql="SELECT o.id,o.order_number,o.order_type, " +
				" s.area_city start_city, " +
				" k.area_city end_city, " +
				" s.departure_time, " +
				" o.order_time, " +
				" o.order_balance, " +
				" c.remain_seat_number, " +
				" c.remain_child_seat_number, " +
				" o.status,o.contact_name,o.contact_phone, " +
				"GROUP_CONCAT(CONCAT_WS('|',x.passenger_name, " +
				" case x.`status` " +
				" when 0 then '未退票' " +
				" when 1 then '已退票' " +
				" when 2 then '已过期' " +
				" end  " +
				"  ) SEPARATOR ';') pe, " +
				"IF(o.refund_balance is null,0,o.refund_balance) refund_balance " +
				"FROM ztc_order o " +
				"INNER JOIN ztc_classes_site_for_sale s ON s.id = o.fk_start_classes_site_for_sale_id " +
				"INNER JOIN ztc_classes_site_for_sale k ON k.id = o.fk_end_classes_site_for_sale_id " +
				"INNER join ztc_order_ticket x on o.id=x.fk_order_id " +
				"LEFT JOIN " +
				" (" +
				"SELECT a.fk_order_id, " +
				" remain_seat_number, IFNULL(remain_child_seat_number, " +
				" 0) remain_child_seat_number " +
				"FROM " +
				" (" +
				"SELECT t.fk_order_id, COUNT(t.id) remain_seat_number " +
				"FROM ztc_order_ticket t " +
				"WHERE t.passenger_type = 0 " +
				"GROUP BY t.fk_order_id) a " +
				"LEFT JOIN ( " +
				"SELECT t.fk_order_id, COUNT(t.id) remain_child_seat_number " +
				"FROM ztc_order_ticket t " +
				"WHERE t.passenger_type = 1 " +
				"GROUP BY t.fk_order_id) b ON a.fk_order_id = b.fk_order_id) c ON o.id=c.fk_order_id " +
				"WHERE o.status > 0 and o.order_number = ? and o.type=0 ";
		return (Map) this.currentSession().createNativeQuery(sql).setParameter(1,orderNumber).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getSingleResult();
	}


		/** 获取当前公司下的所有订单（直通车） */
	public Pager<Map> getAllZcOrderByCompanyId(ZtcTransport ztcTransport, Integer pageSize, Integer pageNumber, String orderId,String startDate,String endDate,String departDateStart,String departDateEnd,String contactName,String contactPhone,String orderStatus) {
		Pager<Map> pager=new Pager<>();
		pager.setPageSize(pageSize);
		pager.setCurrentPage(pageNumber);
		String sql="SELECT o.id,o.order_number, " +
				" s.area_city start_city, " +
				" k.area_city end_city, " +
				" s.departure_time, " +
				" o.order_time, " +
				" o.order_balance, " +
				" c.remain_seat_number, " +
				" c.remain_child_seat_number, " +
				" o.status,o.contact_name,o.contact_phone, " +
				"GROUP_CONCAT(CONCAT_WS('|',x.passenger_name, " +
				" case x.`status` " +
				" when 0 then '未退票' " +
				" when 1 then '已退票' " +
				" when 2 then '已过期' " +
				" end  " +
				"  ) SEPARATOR ';') pe, " +
				"IF(o.refund_balance is null,0,o.refund_balance) refund_balance " +
				"FROM ztc_order o " +
				"INNER JOIN ztc_classes_site_for_sale s ON s.id = o.fk_start_classes_site_for_sale_id ";
				if(departDateStart!=null&&!"".equals(departDateStart)&&departDateEnd!=null&&!"".equals(departDateEnd)){
					sql+=" and s.departure_time >='"+departDateStart+"' and s.departure_time<='"+departDateEnd+" 23:59:59' ";

				}
				sql+="INNER JOIN ztc_classes_site_for_sale k ON k.id = o.fk_end_classes_site_for_sale_id " +
				"INNER join ztc_order_ticket x on o.id=x.fk_order_id " +
				"LEFT JOIN " +
				" (" +
				"SELECT a.fk_order_id, " +
				" remain_seat_number, IFNULL(remain_child_seat_number, " +
				" 0) remain_child_seat_number " +
				"FROM " +
				" (" +
				"SELECT t.fk_order_id, COUNT(t.id) remain_seat_number " +
				"FROM ztc_order_ticket t " +
				"WHERE t.passenger_type = 0 " +
				"GROUP BY t.fk_order_id) a " +
				"LEFT JOIN ( " +
				"SELECT t.fk_order_id, COUNT(t.id) remain_child_seat_number " +
				"FROM ztc_order_ticket t " +
				"WHERE t.passenger_type = 1 " +
				"GROUP BY t.fk_order_id) b ON a.fk_order_id = b.fk_order_id) c ON o.id=c.fk_order_id " +
				"WHERE o.order_type in (1,3) and o.type=0 ";
		if (orderStatus != null && !"".equals(orderStatus)&&!"全部".equals(orderStatus.trim())) {
			switch (orderStatus){
				case "已完成":orderStatus="3";break;
				case "待出行":orderStatus="2";break;
				case "出票中":orderStatus="1";break;
				case "已退款":orderStatus="5";break;
			}
			sql += " and o.status = "+orderStatus;
		} else {
			sql += " and o.status > 0 ";
		}
		if (ztcTransport.getType() != 0) {
			sql += " and o.fk_transport_id='" + ztcTransport.getId() + "'";
		}
		if (orderId != null && !"".equals(orderId.trim())) {
			sql += " and o.order_number like '%" + orderId + "%'";
		}
		if(contactName!=null&&!"".equals(contactName.trim())){
			sql+=" and o.contact_name  like '%"+contactName+"%'";
		}
		if(contactPhone!=null&&!"".equals(contactPhone.trim())){
			sql+=" and o.contact_phone like '%"+contactPhone+"%'";
		}
		if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
			sql+=" and o.order_time >='"+startDate+"' and o.order_time<='"+endDate+" 23:59:59'";
		}
		sql+=" group by o.id order by order_time desc";
		pager.setContent((List<Map>) this.currentSession().createNativeQuery(sql).setFirstResult(pager.getStart()).setMaxResults(pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList());
		pager.setRecordTotal(this.currentSession().createNativeQuery(sql).getResultList().size());
		return pager;
	}

	/** 获取当前公司下的所有订单（直通车） */
	public List<Map> getAllZcOrderByCompanyId(ZtcTransport ztcTransport,String orderId,String startDate,String endDate,String departDateStart,String departDateEnd,String contactName,String contactPhone,String orderStatus) {
		String sql="SELECT o.id,o.order_number, " +
				" s.area_city start_city, " +
				" k.area_city end_city, " +
				" s.departure_time, " +
				" o.order_time, " +
				" o.order_balance, " +
				" c.remain_seat_number, " +
				" c.remain_child_seat_number, " +
				" o.status,o.contact_name,o.contact_phone, " +
				"GROUP_CONCAT(CONCAT_WS('|',x.passenger_name, " +
				" case x.`status` " +
				" when 0 then '未退票' " +
				" when 1 then '已退票' " +
				" when 2 then '已过期' " +
				" end  " +
				"  ) SEPARATOR ';') pe, " +
				"IF(o.refund_balance is null,0,o.refund_balance) refund_balance " +
				"FROM ztc_order o " +
				"INNER JOIN ztc_classes_site_for_sale s ON s.id = o.fk_start_classes_site_for_sale_id ";
		if(departDateStart!=null&&!"".equals(departDateStart)&&departDateEnd!=null&&!"".equals(departDateEnd)){
			sql+=" and s.departure_time >='"+departDateStart+"' and s.departure_time<='"+departDateEnd+" 23:59:59' ";

		}
		sql+="INNER JOIN ztc_classes_site_for_sale k ON k.id = o.fk_end_classes_site_for_sale_id " +
				"INNER join ztc_order_ticket x on o.id=x.fk_order_id " +
				"LEFT JOIN " +
				" (" +
				"SELECT a.fk_order_id, " +
				" remain_seat_number, IFNULL(remain_child_seat_number, " +
				" 0) remain_child_seat_number " +
				"FROM " +
				" (" +
				"SELECT t.fk_order_id, COUNT(t.id) remain_seat_number " +
				"FROM ztc_order_ticket t " +
				"WHERE t.passenger_type = 0 " +
				"GROUP BY t.fk_order_id) a " +
				"LEFT JOIN ( " +
				"SELECT t.fk_order_id, COUNT(t.id) remain_child_seat_number " +
				"FROM ztc_order_ticket t " +
				"WHERE t.passenger_type = 1 " +
				"GROUP BY t.fk_order_id) b ON a.fk_order_id = b.fk_order_id) c ON o.id=c.fk_order_id " +
				"WHERE o.order_type in (1,3) and o.type=0 ";
		if (orderStatus != null && !"".equals(orderStatus)&&!"全部".equals(orderStatus.trim())) {
			switch (orderStatus){
				case "已完成":orderStatus="3";break;
				case "待出行":orderStatus="2";break;
				case "出票中":orderStatus="1";break;
				case "已退款":orderStatus="5";break;
			}
			sql += " and o.status = "+orderStatus;
		} else {
			sql += " and o.status > 0 ";
		}
		if (ztcTransport.getType() != 0) {
			sql += " and o.fk_transport_id='" + ztcTransport.getId() + "'";
		}
		if (orderId != null && !"".equals(orderId.trim())) {
			sql += " and o.order_number like '%" + orderId + "%'";
		}
		if(contactName!=null&&!"".equals(contactName.trim())){
			sql+=" and o.contact_name  like '%"+contactName+"%'";
		}
		if(contactPhone!=null&&!"".equals(contactPhone.trim())){
			sql+=" and o.contact_phone like '%"+contactPhone+"%'";
		}
		if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
			sql+=" and o.order_time >='"+startDate+"' and o.order_time<='"+endDate+" 23:59:59'";
		}
		sql+=" group by o.id order by order_time desc";
		return this.currentSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
	}

	/** 获取当前公司下的所有订单（充值） */
	public Pager<Map> getRechargeList(Integer pageSize, Integer pageNumber, String orderId, Integer payType,String startDate,String endDate) {
		Pager<Map> pager=new Pager<>();
		pager.setPageSize(pageSize);
		pager.setCurrentPage(pageNumber);
		//充值订单
		String sql="SELECT z.order_number,z.`status`,z.order_balance,z.pay_type,z.pay_time,u.name,z.pay_balance " +
					"FROM ztc_order z " +
					"INNER JOIN app_user u ON z.fk_user_id=u.id where z.type=1 and z.`status`>0 ";
	    if(orderId!=null&&!"".equals(orderId.trim())){
			sql+=" and z.order_number like '%"+orderId+"%'";
		}
		if(payType!=0){
			sql+=" and z.pay_type='"+payType+"'";
		}
		if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
			sql+=" and z.pay_time >='"+startDate+"' and z.pay_time<='"+endDate+" 23:59:59'";
		}
		sql+=" order by pay_time desc";
		pager.setContent((List<Map>) this.currentSession().createNativeQuery(sql).setFirstResult(pager.getStart()).setMaxResults(pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList());
		pager.setRecordTotal(this.currentSession().createNativeQuery(sql).getResultList().size());
		return pager;
	}

	/** 获取当前公司下的所有订单（去哪儿） */
	public Pager<Map> getCtripList(Integer pageSize, Integer pageNumber, String orderId, Integer payType,String startDate,String endDate) {
		Pager<Map> pager=new Pager<>();
		pager.setPageSize(pageSize);
		pager.setCurrentPage(pageNumber);
		String sql="SELECT z.id,x.from_city,x.to_city, z.order_number,z.`status`,z.order_balance,c.remain_seat_number, " +
				"c.remain_child_seat_number, IFNULL(0,z.refund_balance) refund_balance,z.contact_name,z.contact_phone,x.departure_date,z.order_time, GROUP_CONCAT(CONCAT_WS('|',ti.passenger_name) SEPARATOR ';') person " +
				"FROM ztc_order z " +
				"inner join ztc_order_ticket ti on z.id=ti.fk_order_id " +
				"inner join ztc_ctrip_data x on z.id=x.fk_order_id " +
				"LEFT JOIN ( " +
				"SELECT a.fk_order_id, remain_seat_number, IFNULL(remain_child_seat_number, 0) remain_child_seat_number " +
				"FROM " +
				"( " +
				"SELECT t.fk_order_id, COUNT(t.id) remain_seat_number " +
				"FROM ztc_order_ticket t " +
				"WHERE t.passenger_type = 0 " +
				"GROUP BY t.fk_order_id) a " +
				"LEFT JOIN ( " +
				"SELECT t.fk_order_id, COUNT(t.id) remain_child_seat_number " +
				"FROM ztc_order_ticket t " +
				"WHERE t.passenger_type = 1 " +
				"GROUP BY t.fk_order_id) b ON a.fk_order_id = b.fk_order_id) c ON z.id=c.fk_order_id " +
				"WHERE z.order_type=4 and z.status>=0 ";
		if(orderId!=null&&!"".equals(orderId.trim())){
			sql+=" and z.order_number like '%"+orderId+"%'";
		}
		if(payType!=0){
			sql+=" and z.pay_type='"+payType+"'";
		}
		if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
			sql+=" and z.order_time >='"+startDate+"' and z.order_time<='"+endDate+" 23:59:59'";
		}
		sql+=" group by z.id order by z.order_time desc";
		pager.setContent((List<Map>) this.currentSession().createNativeQuery(sql).setFirstResult(pager.getStart()).setMaxResults(pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList());
		pager.setRecordTotal(this.currentSession().createNativeQuery(sql).getResultList().size());
		return pager;
	}


		/** 获取订单详情 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getOrderDetail(String orderId, Integer orderType, Integer type) {
		String sql="SELECT com.full_name,concat_ws('-',s.area_city,s1.area_city) name,z.order_number,z.`status`,z.order_balance,z.pay_balance,z.refund_balance,z.contact_name,z.contact_phone,z.`type`,z.pay_type,z.order_time,z.pay_time,z.finish_time,z.refund_time,z.refund_reason,GROUP_CONCAT(CONCAT_WS(" +
				"',',ti.passenger_name," +
				"ifnull(ti.passenger_birthday,'')," +
				"ti.passenger_type," +
				"ti.passenger_card_number," +
				"ti.passenger_card_type," +
				"ifnull(ti.seat_number,'')," +
				"ifnull(ti.seat_type,'')," +
				"ti.price) SEPARATOR ';') passenger," +
				"z.id, " +
				"z.order_type " +
				"FROM ztc_order z " +
				"INNER JOIN ztc_classes_site_for_sale s ON s.id=z.fk_start_classes_site_for_sale_id " +
				"INNER JOIN ztc_classes_site_for_sale s1 ON s1.id=z.fk_end_classes_site_for_sale_id " +
				"INNER JOIN ztc_transport com ON z.fk_transport_id=com.id " +
				"INNER JOIN ztc_order_ticket ti ON z.id=ti.fk_order_id where z.id='"+orderId+"'";

		//顺风车订单
		if(type==3){
			sql="SELECT com.name full_name,concat_ws('-',s.area_city,s1.area_city) name,z.order_number,z.`status`,z.order_balance,z.pay_balance,z.refund_balance,z.contact_name,z.contact_phone,z.`type`,z.pay_type,z.order_time,z.pay_time,z.finish_time,z.refund_time,z.refund_reason,GROUP_CONCAT(CONCAT_WS( \n" +
				"',',ti.passenger_name, \n" +
				"ifnull(ti.passenger_birthday,''), \n" +
				"ti.passenger_type, \n" +
				"ti.passenger_card_number, \n" +
				"ti.passenger_card_type, \n" +
				"ifnull(ti.seat_number,''), \n" +
				"ifnull(ti.seat_type,''), \n" +
				"ti.price) SEPARATOR ';') passenger, \n" +
				"z.id, \n" +
				"z.order_type " +
				"FROM ztc_order z  \n" +
				"INNER JOIN ztc_classes_site_for_sale s ON s.id=z.fk_start_classes_site_for_sale_id  \n" +
				"INNER JOIN ztc_classes_site_for_sale s1 ON s1.id=z.fk_end_classes_site_for_sale_id  \n" +
				"INNER JOIN app_user com ON z.fk_transport_id=com.id  \n" +
				"INNER JOIN ztc_order_ticket ti ON z.id=ti.fk_order_id where z.id='"+orderId+"'";
		}
		//充值订单
		if(orderType==1){
			sql="SELECT z.order_number,z.`status` sta,z.order_balance,z.pay_balance,z.contact_name,z.contact_phone,z.`type`,z.pay_type,z.order_time,z.pay_time,z.finish_time,u.name,u.real_name,u.phone,u.`status` " +
				"FROM ztc_order z " +
				"INNER JOIN app_user u ON z.fk_user_id=u.id AND z.id='"+orderId+"'";
		}
		//去哪儿订单
		if(type==4){
			sql="SELECT '去哪儿单',CONCAT_WS('-',x.from_city,x.to_city), z.order_number,z.`status`,z.order_balance,z.pay_balance,z.refund_balance,z.contact_name,z.contact_phone,z.`type`,z.pay_type,z.order_time,z.pay_time,z.finish_time,z.refund_time,z.refund_reason, GROUP_CONCAT(CONCAT_WS(',',ti.passenger_name,ifnull(ti.passenger_birthday,''),ifnull(ti.passenger_type,''),ifnull(ti.passenger_card_number,''),ti.passenger_card_type,ifnull(ti.seat_number,''),ifnull(ti.seat_type,''),ti.price) SEPARATOR ';') passenger,z.id " +
				"FROM ztc_order z,ztc_order_ticket ti,ztc_ctrip_data x WHERE z.id=ti.fk_order_id and z.id=x.fk_order_id and z.id='"+orderId+"'";
		}
		return this.createSQLQuery(sql).getResultList();
	}

	public String delete(String id, String transportId, String walletId, String priceId) {
		String sql = "delete from ztc_order where 1=1";
		if (id != null) {
			sql += " and id='" + id + "'";
		}
		if (transportId != null) {
			sql += " and fk_ztc_transport_id='" + transportId + "'";
		}
		if (walletId != null) {
			sql += " and fk_ztc_wallet_id='" + walletId + "'";
		}
		if (priceId != null) {
			sql += " and fk_ztc_classes_price_id='" + priceId + "'";
		}
		if (this.currentSession().createNativeQuery(sql).executeUpdate() >= 0) {
			return "1";
		}
		return "0";
	}

	public List<ZtcOrder> searchInfo(ZtcOrder ztcOrder) {
		String sql = "select * from ztc_order  where 1=1";
		if (ztcOrder.getOrderNumber() != null) {
			sql += " and order_number='" + ztcOrder.getOrderNumber() + "'";
		}
		if (ztcOrder.getFkZtcWalletId() != null) {
			sql += " and ztc_wallet_id='" + ztcOrder.getFkZtcWalletId() + "'";
		}
		if (ztcOrder.getFkZtcClassesPriceForSaleId() != null) {
			sql += " and fk_ztc_classes_price_for_sale_id='" + ztcOrder.getFkZtcClassesPriceForSaleId() + "'";
		}
		if (ztcOrder.getStatus() != null) {
			sql += " and status='" + ztcOrder.getStatus() + "'";
		}
		if (ztcOrder.getId() != null) {
			sql += " and id='" + ztcOrder.getId() + "'";
		}
		List<ZtcOrder> list = this.currentSession().createNativeQuery(sql,ZtcOrder.class).getResultList();
		return list;
	}



	public String update(ZtcOrder ztcOrder){
		ZtcOrder ztcOrderBean = this.findUniqueBy("id", ztcOrder.getId());
		if (ztcOrder.getStatus() != null) {
			ztcOrderBean.setStatus(ztcOrder.getStatus());
		}
		if (ztcOrder.getPayType() != null) {
			ztcOrderBean.setPayType(ztcOrder.getPayType());
		}
		if (ztcOrder.getPayTime() != null) {
			ztcOrderBean.setPayTime(ztcOrder.getPayTime());
		}
		if (ztcOrder.getFinishTime() != null) {
			ztcOrderBean.setFinishTime(ztcOrder.getFinishTime());
		}
		if (ztcOrder.getRefundTime() != null) {
			ztcOrderBean.setRefundTime(ztcOrder.getRefundTime());
		}
		if (ztcOrder.getRefundBalance() != null) {
			ztcOrderBean.setRefundBalance(ztcOrder.getRefundBalance());
		}
		if (ztcOrder.getRefundReason() != null) {
			ztcOrderBean.setRefundReason(ztcOrder.getRefundReason());
		}
		this.save(ztcOrderBean);
		if (ztcOrderBean.getId() != null) {
			return "1";
		}
		return "0";
	}


	public Map getCtripPayDetailById(String id){
		String sql = "select o.*,d.car_type,d.departure_date,d.from_city,d.to_city,d.ctrip_order_number from ztc_order o left join\n" +
				"        ztc_ctrip_data d on o.id = d.fk_order_id\n" +
				"        where " +
				"            o.id = ?";
		return (Map)this.currentSession().createNativeQuery(sql).setParameter(1,id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getSingleResult();
	}

	public Map getOrderById(String id){
		String sql = "SELECT o.id,\n" +
				"        s.area_city start_city,\n" +
				"        k.area_city end_city,\n" +
				"        s.departure_time,\n" +
				"        o.order_balance price,\n" +
				"        c.remain_seat_number,\n" +
				"        c.remain_child_seat_number,\n" +
				"        y.id classes_for_sale_id,\n" +
				"        y.car_type,\n" +
				"        o.status,\n" +
				"        o.contact_phone,\n" +
				"        o.refund_balance refund_price,\n" +
				"        o.refund_time,\n" +
				"        ifnull(o.order_type,1) order_type\n" +
				"        FROM ztc_order o\n" +
				"        INNER JOIN ztc_classes_site_for_sale s\n" +
				"        ON s.id = o.fk_start_classes_site_for_sale_id\n" +
				"        INNER JOIN ztc_classes_site_for_sale k\n" +
				"        ON k.id = o.fk_end_classes_site_for_sale_id\n" +
				"        INNER JOIN ztc_classes_for_sale y\n" +
				"        ON y.id = o.fk_classes_for_sale_id\n" +
				"        LEFT JOIN\n" +
				"        (SELECT a.fk_order_id,\n" +
				"        remain_seat_number,\n" +
				"        ifnull(remain_child_seat_number,\n" +
				"        0) remain_child_seat_number\n" +
				"        FROM\n" +
				"        (SELECT t.fk_order_id,\n" +
				"        count(t.id) remain_seat_number\n" +
				"        FROM ztc_order_ticket t\n" +
				"        WHERE t.passenger_type = 0\n" +
				"        GROUP BY t.fk_order_id ) a\n" +
				"        LEFT JOIN\n" +
				"        (SELECT t.fk_order_id,\n" +
				"        count(t.id) remain_child_seat_number\n" +
				"        FROM ztc_order_ticket t\n" +
				"        WHERE t.passenger_type = 1\n" +
				"        GROUP BY t.fk_order_id ) b\n" +
				"        ON a.fk_order_id = b.fk_order_id) c\n" +
				"        ON o.id=c.fk_order_id\n" +
				"        where\n" +
				"            o.id = ?\n" ;
		return (Map)this.currentSession().createNativeQuery(sql).setParameter(1,id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getSingleResult();
	}

	public Map getPrivateOrderById(String id){
		String sql = "SELECT o.id,\n" +
				"        o.order_number,\n" +
				"        o.fk_ztc_classes_price_for_sale_id,\n" +
				"        s.area_city from_city_name,\n" +
				"        k.area_city to_city_name,\n" +
				"        s.name from_station_name,\n" +
				"        k.name to_station_name,\n" +
				"        s.lat from_station_lat,\n" +
				"        k.lat to_station_lat,\n" +
				"        s.departure_time,\n" +
				"        o.order_balance price,\n" +
				"        c.remain_seat_number,\n" +
				"        c.remain_child_seat_number,\n" +
				"        y.id classes_for_sale_id,\n" +
				"        y.car_type,\n" +
				"        o.status,\n" +
				"        o.contact_phone,\n" +
				"        a.passenger_name,\n" +
				"        a.passenger_type,\n" +
				"        a.passenger_birthday,\n" +
				"        a.passenger_card_number,\n" +
				"        a.passenger_card_type,\n" +
				"        a.price single_price,\n" +
				"        o.refund_balance refund_price,\n" +
				"        o.refund_time,\n" +
				"        y.remark,\n" +
				"        s.manager_phone driver_phone,\n" +
				"        ifnull(o.order_type,1) order_type,\n" +
				"        y.type,\n" +
				"        IFNULL(r.car_number,'') car_number,\n" +
				"        IFNULL(r.color,'') color\n" +
				"        FROM ztc_order o\n" +
				"        INNER JOIN ztc_classes_site_for_sale s\n" +
				"        ON s.id = o.fk_start_classes_site_for_sale_id\n" +
				"        INNER JOIN ztc_classes_site_for_sale k\n" +
				"        ON k.id = o.fk_end_classes_site_for_sale_id\n" +
				"        INNER JOIN ztc_classes_for_sale y\n" +
				"        ON y.id = o.fk_classes_for_sale_id\n" +
				"        LEFT JOIN ztc_car r\n" +
				"        ON r.id = y.fk_ztc_car_id\n" +
				"        LEFT JOIN ztc_order_ticket a\n" +
				"        ON a.fk_order_id = o.id\n" +
				"        LEFT JOIN\n" +
				"        (SELECT a.fk_order_id,\n" +
				"        remain_seat_number,\n" +
				"        ifnull(remain_child_seat_number,\n" +
				"        0) remain_child_seat_number\n" +
				"        FROM\n" +
				"        (SELECT t.fk_order_id,\n" +
				"        count(t.id) remain_seat_number\n" +
				"        FROM ztc_order_ticket t\n" +
				"        WHERE t.passenger_type = 0\n" +
				"        GROUP BY t.fk_order_id ) a\n" +
				"        LEFT JOIN\n" +
				"        (SELECT t.fk_order_id,\n" +
				"        count(t.id) remain_child_seat_number\n" +
				"        FROM ztc_order_ticket t\n" +
				"        WHERE t.passenger_type = 1\n" +
				"        GROUP BY t.fk_order_id ) b\n" +
				"        ON a.fk_order_id = b.fk_order_id) c\n" +
				"        ON o.id=c.fk_order_id\n" +
				"        where \n" +
				"            o.id = ?\n";
		return (Map)this.currentSession().createNativeQuery(sql).setParameter(1,id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getSingleResult();
	}


	public Map getPenaltyOrderById(String id){
		String sql = "SELECT o.id,o.order_number,o.order_balance,o.order_type,o.fk_transport_id,t.type,t.parent_id,p.income_scale\n" +
				"        FROM ztc_order o\n" +
				"        INNER JOIN ztc_transport t\n" +
				"        ON t.id = o.fk_transport_id\n" +
				"        INNER JOIN ztc_transport_percentage_scale p\n" +
				"        ON t.id = p.transport_id\n" +
				"        where " +
				"            o.id = ?\n" ;
		return (Map)this.currentSession().createNativeQuery(sql).setParameter(1,id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getSingleResult();
	}

	/**
	 * 根据公司ID获取最新N条订单信息.
	 * @param tid  公司ID
	 * @param number 条数(6)
	 * @return
	 */
	public List<Map> getUpZtcOrderListByTid(String tid,Integer number){
		String sql="SELECT o.contact_name,concat_ws('-',s.area_city,s1.area_city) city,o.id,o.`status`,o.order_time,o.order_balance " +
					"FROM ztc_order o " +
					"inner join ztc_classes_site_for_sale s on s.id=o.fk_start_classes_site_for_sale_id " +
					"inner join ztc_classes_site_for_sale s1 on s1.id=o.fk_end_classes_site_for_sale_id " +
					"WHERE o.fk_transport_id=? AND o.`status` IN (2,5,6) " +
					"ORDER BY o.order_time DESC LIMIT ? ";
		return this.currentSession().createNativeQuery(sql).setParameter(1,tid).setParameter(2,number).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
	}

	/**
	 * 获取快递订单列表.
	 *
	 * @param pageSize    每页条数
	 * @param pageNumber  页码
	 * @return
	 */
    public Pager<Map> getExpressZtcOrderList(Integer pageSize, Integer pageNumber,String orderNumber,String startDate,String endDate) {
		Pager<Map> pager=new Pager<>();
		pager.setPageSize(pageSize);
		pager.setCurrentPage(pageNumber);
		String sql="SELECT e.id,e.shipping_name,e.shipping_phone,e.shipping_location_address, " +
				   "e.shipping_detailed_address,e.receipt_name,e.receipt_phone,e.receipt_location_address,e.receipt_detailed_address, " +
				   "e.goods_name,e.transport_type,e.transport_mode,e.order_fee,e.release_time,e.order_number,e.pay_type,e.`status` " +
				   "FROM express_zc_release e " +
				   "WHERE e.`status`<>-1 ";
		if(orderNumber!=null&&!"".equals(orderNumber.trim())){
			sql+=" and e.order_number like '%"+orderNumber+"%'";
		}
		if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
			sql+=" and e.release_time >='"+startDate+"' and e.release_time<='"+endDate+" 23:59:59'";
		}
		pager.setRecordTotal(this.currentSession().createNativeQuery(sql).getResultList().size());
		sql+=" ORDER BY e.release_time DESC limit ?,? ";
		pager.setContent(this.currentSession().createNativeQuery(sql).setParameter(1,pager.getStart()).setParameter(2,pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList());
		return pager;
    }

	/**
	 * 获取快递订单详情.
	 *
	 * @return
	 */
	public Map getExpressZtcOrderDetail(String oid) {
		String sql="SELECT * FROM express_zc_release e WHERE e.id=? ";
		List<Object[]> object=this.currentSession().createNativeQuery(sql).setParameter(1,oid).getResultList();
		if(object.size()<=0){
			return null;
		}
		return (Map)this.currentSession().createNativeQuery(sql).setParameter(1,oid).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getSingleResult();
	}
}